package com.szholly.pro.watergas_server.ps.schedule;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import com.szholly.data.general.RowBase;
import com.szholly.pro.watergas_server.common.data.DataUtil;
import org.apache.log4j.Logger;
import org.quartz.Job;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;

import lrq.common.MyLog;

/**
 * 有效性在线周报定时任务
 * @author 
 *
 */
public class CreateValidationWeekData implements Job{
	
	Logger log = MyLog.getLogger("CreateValidationWeekData");//日志名称
	@Override
	public void execute(JobExecutionContext arg0) throws JobExecutionException {
		System.out.println("开始执行任务！");
		SimpleDateFormat fm = new SimpleDateFormat("yyyy-MM-dd"); 
		SimpleDateFormat fmday = new SimpleDateFormat("yyyy-MM"); 
		Date date=new Date();		
		String start=fmday.format(date)+"-"+(date.getDate()-6)+" 00:00:00";
		String enddata=fm.format(date)+" 23:59:59";
		String waterType="4";
		GetValidationWeekData(start,enddata,waterType);
		System.out.println("任务结束！");
	}

	/**
	 * 获取有效性在线周报数据
	 */
	private void GetValidationWeekData(String startdata,String enddata,String waterType){
		try {
			//获取省份sql
			String proSql="select t.s_domaincode,t.s_pid from data_defulatdomain t where t.s_domainname='行政区划' and t.i_jb='1'";		
			List<RowBase> proList= DataUtil.getSelectRows(proSql, null);
			for(RowBase row:proList){
				//获取城市SQL语句
				String citySql="select t.s_domaincode,t.s_pid from data_defulatdomain t where t.s_domainname='行政区划' and t.s_pid='"+row.get("S_DOMAINCODE")+"'";
				List<RowBase> cityList=DataUtil.getSelectRows(citySql, null);		
				for(RowBase cityRow:cityList){
					int proCount=0;//市超标记录数
					int siteCount=0;//市监控点位数量
					//获取区域
					String areaSql="select t.s_domaincode,t.s_pid from data_defulatdomain t where t.s_domainname='行政区划' and t.s_pid='"+cityRow.get("S_DOMAINCODE")+"'";
					List<RowBase> areaList=DataUtil.getSelectRows(areaSql, null);
					for(RowBase arearow:areaList){
						int cityCount=0;//区域超标记录数
						//获取区域站点
						String siteSql="select t.code,t.name from bc_site_info  t where t.station_category='"+waterType+"' and t.area_code='"+arearow.get("S_DOMAINCODE")+"'";
						List<RowBase> siteList=DataUtil.getSelectRows(siteSql, null);
						for(RowBase siteRow:siteList){
							String sql="select count(*) from BD_MO_HOUR t"
									  +" left join BC_POLL_THRESHOLD t1 on t1.mn=t.mn and t1.pollute_code=t.parmname"
									  +" where t.mn='"+siteRow.get("CODE")+"'  and t.datatime>=to_date('"+startdata+"','yyyy-mm-dd hh24:mi:ss')"
									  +" and t.datatime<=to_date('"+enddata+" ','yyyy-mm-dd hh24:mi:ss') and (t.avg<t1.density_alarm_dowmlimit or t.avg>t1.density_alarm_uplimit)";
							int count=DataUtil.getScaleInt(sql, null);
							cityCount=cityCount+count;//区域超标记录数	
						}
						String inscitySql="insert into bd_mo_vdm_month"
								+" (province_code, city_code, area_code, datatime, watertype, statisticstype, overproofcount,DISGRADE,SITECOUNT)"
								+" values ('"+row.get("S_DOMAINCODE")+"','"+cityRow.get("S_DOMAINCODE")+"','"+arearow.get("S_DOMAINCODE")+"', "
								+" to_date('"+startdata+"','yyyy-mm-dd hh24:mi:ss'),'"+waterType+"','周',"+cityCount+",'3',"+siteList.size()+")";
						DataUtil.ExecuteUpdateSql(inscitySql, null);	
						proCount=proCount+cityCount;//市超标记录数	
						siteCount=siteCount+siteList.size();
					}
					String inscitySql="insert into bd_mo_vdm_month"
							+" (province_code, city_code, datatime, watertype, statisticstype, overproofcount,DISGRADE,SITECOUNT)"
							+" values ('"+row.get("S_DOMAINCODE")+"','"+cityRow.get("S_DOMAINCODE")+"',"
							+" to_date('"+startdata+"','yyyy-mm-dd hh24:mi:ss'),'"+waterType+"','周',"+proCount+",'2',"+siteCount+")";
					DataUtil.ExecuteUpdateSql(inscitySql, null);
				}				
			}
		} catch (Exception e) {
			log.error("有效性在线月报统计结果入库出错", e);
		}
	
	}
}
